package Dao;

import Entity.Merchandise;
import Entity.Merchant;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class MctDao {
    private static Connection connection = null;

    private static void openDB() throws ClassNotFoundException, SQLException {
        // JDBC访问MySQL数据库
        // 1. 将连接MySQL的Jar包引入到工程中
        // 2. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 3. 获取数据库连接
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/ecommerce?characterEncoding=utf-8&serverTimezone=GMT%2B8",
                "root",
                "lty63481960");
    }

    private static void closeDB() throws SQLException {
        connection.close();
    }

    public static List<Merchant> queryByKey(String key, String username) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = " ";
        if(username==null||username.isEmpty()) sql = "select * from merchant order by id desc";
        else sql = "select * from merchant where username='" + username +"' order by id desc";
        // 5. 创建Statement
        Statement statement = connection.createStatement();
        // 6. 执行Statement
        ResultSet resultSet = statement.executeQuery(sql);
        // 7. 处理结果集
        ArrayList<Merchant> list = new ArrayList<>();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("mctname");
            String belongto = resultSet.getString("belongto");
            Merchant merchant = new Merchant(id, name, belongto, username);

            list.add(merchant);
        }
        if(key!=null||!key.isEmpty()) Collections.sort(list, new Comparator<Merchant>() {
            @Override
            public int compare(Merchant o1, Merchant o2) {
                if(String.valueOf(o1.getId()).equals(key)) return -1;
                if(String.valueOf(o2.getId()).equals(key)) return 1;
                if(o1.getName().contains(key)&&o2.getName().contains(key)&&o1.getName().length()!=o2.getName().length())
                    return o1.getName().length() - o2.getName().length();
                if(o1.getName().contains(key)) return -1;
                if(o2.getName().contains(key)) return 1;
                if(o1.getBelongto().contains(key)&&o2.getBelongto().contains(key)&&o1.getBelongto().length()!=o2.getBelongto().length())
                    return o1.getBelongto().length() - o2.getBelongto().length();
                if(o1.getBelongto().contains(key)) return -1;
                if(o2.getBelongto().contains(key)) return 1;

                if(String.valueOf(o1.getId()).contains(key)) return -1;
                if(String.valueOf(o2.getId()).contains(key)) return -1;
                return o2.getId()-o1.getId();
            }
        });
        // 8. 关闭资源
        resultSet.close();
        statement.close();
        closeDB();

        return list;
    }
    public static Merchant queryByid(int id) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "select * from merchant where id=" + id +" order by id desc";
        // 5. 创建Statement
        Statement statement = connection.createStatement();
        // 6. 执行Statement
        ResultSet resultSet = statement.executeQuery(sql);
        // 7. 处理结果集
        Merchant merchant = null;
        if(resultSet.next()){
            String name = resultSet.getString("mctname");
            String belongto = resultSet.getString("belongto");
            String username = resultSet.getString("username");
            merchant = new Merchant(id, name, belongto, username);
//            System.out.println(merchant);
        }
        // 8. 关闭资源
        resultSet.close();
        statement.close();
        closeDB();

        return merchant;
    }
//    public static List<Merchant> queryByname(String name) throws SQLException, ClassNotFoundException {
//        openDB();
//        // 4. 编写查询city表的sql语句
//        String sql = "select * from merchant where mctname='"+ name +"'";
//        // 5. 创建Statement
//        Statement statement = connection.createStatement();
//        // 6. 执行Statement
//        ResultSet resultSet = statement.executeQuery(sql);
//        // 7. 处理结果集
//        ArrayList<Merchant> list = new ArrayList<>();
//
//        while(resultSet.next()){
//            int id = resultSet.getInt("id");
//            String belongto = resultSet.getString("belongto");
//            Merchant merchant = new Merchant(id, name, belongto);
//
//            list.add(merchant);
//        }
//        // 8. 关闭资源
//        resultSet.close();
//        statement.close();
//        closeDB();
//
//        return list;
//    }
    public static void update(Merchant merchant) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "update merchant set mctname=?, belongto=? where id=?";
        // 5. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6. 设置参数

        preparedStatement.setString(1, merchant.getName());
        preparedStatement.setString(2, merchant.getBelongto());
        preparedStatement.setInt(3, merchant.getId());
        // 7. 执行修改
        preparedStatement.execute();

        // 8. 关闭资源
        preparedStatement.close();
        closeDB();
    }

    public static void save(Merchant merchant) throws SQLException, ClassNotFoundException {
//        System.out.println(merchant);
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "insert into merchant (mctname, belongto, username) value (?, ?, ?)";
        // 5. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6. 设置参数
//        preparedStatement.setInt(1, merchant.getId());
        preparedStatement.setString(1, merchant.getName());
        preparedStatement.setString(2, merchant.getBelongto());
        preparedStatement.setString(3, merchant.getUsername());

        // 7. 执行插入
        preparedStatement.execute();

        // 8. 关闭资源
        preparedStatement.close();
        closeDB();
    }

    public static void del(int id) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "delete from merchant where id = ?";
        // 5. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6. 设置参数
        preparedStatement.setInt(1, id);
        // 7. 执行删除
        preparedStatement.execute();
        // 8. 关闭资源
        preparedStatement.close();
        closeDB();
    }
}
